# -*- coding: utf-8 -*-

# @Author: Chen Hua
# @Date  : 2020/04/07
# @Desc  : Mysql 链接类
# -*- coding: utf-8 -*-
import datetime
import types
from collections import namedtuple

from sqlalchemy import Column, String, create_engine, DateTime, Date, Integer, Numeric
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from conf.settings import DB_URL

'''
初始化全局db对象
'''
# 创建对象的基类:
Base = declarative_base()
# 初始化数据库连接:
engine = create_engine(DB_URL)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)


# 定义JobConfig对象:
class JobConfig(Base):
    # 表的名字:
    __tablename__ = 'job_config'

    # 表的结构:
    job_name = Column(String(50), primary_key=True)
    job_mode = Column(String(20))
    last_execute_time = Column(DateTime())
    last_data_at = Column(Date())


# 每周新发行基金数量&金额信息:
class WeeklyFund(Base):
    # 表的名字:
    __tablename__ = 'weekly_new_fund'

    # 表的结构:
    sz_cnt = Column(Integer())
    sh_cnt = Column(Integer())
    total_cnt = Column(Integer())
    sz_amt = Column(Numeric())
    sh_amt = Column(Numeric())
    total_amt = Column(Numeric())
    dt = Column(DateTime(), primary_key=True)

    @classmethod
    def dict_save(cls, dic, session=None):
        wf = WeeklyFund();
        for k, v in dic.items():
            wf.__setattr__(k, v)
        if wf.dt is None:
            wf.dt = datetime.datetime.now().date()
        close_session_flag = False;
        try:
            if session is None:
                close_session_flag = True
                session = DBSession()
            session.merge(wf);
            session.commit();
        except Exception as e:
            print('Dict保存WeeklyFund失败.')
            print(str(e))
        finally:
            if close_session_flag:
                session.close()


# 定义InvestorAccount对象:
class InvestorAccount(Base):
    # 表的名字:
    __tablename__ = 'investor_account'

    # 表的结构:
    weekly_new = Column(Integer())
    total = Column(Integer())
    dt = Column(DateTime(), primary_key=True)


# 定义IndexValue对象:
class IndexValue(Base):
    # 表的名字:
    __tablename__ = 'index_value_info'

    # 表的结构:
    index_code = Column(String(), primary_key=True)
    index_name = Column(String())
    pe_ttm_weighted_avg = Column(Numeric())
    pe_ttm_equal_avg = Column(Numeric())
    pe_ttm_median = Column(Numeric())
    pe_ttm_ewpvo = Column(Numeric())
    pb_weighted_avg = Column(Numeric())
    pb_equal_avg = Column(Numeric())
    pb_median = Column(Numeric())
    pb_ewpvo = Column(Numeric())
    dt = Column(DateTime(), primary_key=True)
    pe_ttm_weighted_val_pos = Column(Numeric())
    pe_ttm_equal_val_pos = Column(Numeric())
    pe_ttm_median_val_pos = Column(Numeric())
    pe_ttm_ewpvo_val_pos = Column(Numeric())
    pb_weighted_val_pos = Column(Numeric())
    pb_equal_val_pos = Column(Numeric())
    pb_median_val_pos = Column(Numeric())
    pb_ewpvo_val_pos = Column(Numeric())
    cp = Column(Numeric())
    mc = Column(Numeric())
    cmc = Column(Numeric())

# 定义CnGDP对象:
class CnGDP(Base):
    # 表的名字:
    __tablename__ = 'cn_gdp'
    # 表的结构:
    dt = Column(DateTime(), primary_key=True)  #日期
    mc_ratio = Column(Numeric())   # 证券化率= 证券总值/gdp总值
    cmc_ratio = Column(Numeric())   # 证券化率= 证券流通总值/gdp总值
    gdp = Column(Numeric())   # gdp 值
    mc = Column(Numeric())    # 证券总值
    cmc = Column(Numeric())    # 证券流通总值

# 定义CnRzrq对象:
'''
本日融资余额(元)=前日融资余额＋本日融资买入-本日融资偿还额
本日融券余量(股)=前日融券余量＋本日融券卖出量-本日融券买入量-本日现券偿还量
本日融券余额(元)=本日融券余量×本日收盘价
本日融资融券余额(元)=本日融资余额＋本日融券余额
'''
class CnRzrq(Base):
    # 表的名字:
    __tablename__ = 'cn_rzrq'
    # 表的结构:
    dt = Column(DateTime(), primary_key=True)  #交易日期
    exchange = Column(String(), primary_key=True)   # 交易所代码（SSE上交所SZSE深交所）
    rzye = Column(Numeric())   # 融资余额(元)
    rzmre = Column(Numeric())   # 融资买入额(元)
    rzche = Column(Numeric())    # 融资偿还额(元)
    rqye = Column(Numeric())    # 融券余额(元) 
    rqmcl = Column(Numeric())    # 融券卖出量(股,份,手)
    rzrqye = Column(Numeric())    # 融资融券余额(元)
    rqyl = Column(Numeric())    # 融券余量(股,份,手)


class Xwlb(Base):
    # 表的名字: 新闻联播信息
    __tablename__ = 'stock_xwlb_news'
    # 表的结构:
    dt = Column(DateTime(), primary_key=True)  #日期
    title = Column(String(), primary_key=True)   # 标题
    content = Column(String())   # 正文

class TwoColorBall(Base):
    # 表的名字: 双色球
    __tablename__ = 'two_color_ball'
    # 表的结构:
    dt = Column(DateTime(), primary_key=True)  #日期
    sn = Column(String())  #期号
    r1 = Column(Integer())
    r2 = Column(Integer())
    r3 = Column(Integer())
    r4 = Column(Integer())
    r5 = Column(Integer())
    r6 = Column(Integer())
    b1 = Column(Integer())
